﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace OnlineTestDBHELPER
{
    /// <summary>
    /// DBHELPER 数据库类
    /// </summary>
    public class SqlHelper
    {

        /// <summary>
        /// SqlHelper类构造函数
        /// </summary>
        public SqlHelper()
        {
        }

        #region  全局设置
        /// <summary>
        /// 连接字符串
        /// </summary>
        private static string connectionstring = ConfigurationManager.ConnectionStrings["OnlineTestConnectionString"].ToString();
        /// <summary>
        /// 连接对象
        /// </summary>
        private static SqlConnection sqlconnection = new SqlConnection(connectionstring);
        #endregion

        #region   数据库的打开与关闭
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private static void SQLOpen()
        {
            try
            {
                if (sqlconnection.State == ConnectionState.Open)
                {
                    sqlconnection.Close();
                }
                if (sqlconnection.State != ConnectionState.Open)
                {
                    sqlconnection.Open();
                }
            }
            catch (Exception e)
            {
                sqlconnection.Dispose();
                throw new Exception("打开数据库失败：" + e.Message);

            }
        }
        /// <summary>
        /// 关闭数据库的连接
        /// </summary>
        private static void SQLClose()
        {
            try
            {
                if (sqlconnection.State == ConnectionState.Open)
                {
                    sqlconnection.Close();
                }

            }
            catch (Exception e)
            {
                sqlconnection.Dispose();
                throw new Exception("数据库关闭失败：" + e.Message);
            }
        }
        #endregion

        #region   ExecuteNonQuery执行数据库的各种操作,并返回一个int型的数据
        /// <summary>
        /// 执行不返回数据行的操作，返回一个int类型的数据
        /// </summary>
        /// <param name="sql">要执行的SQL文本命令</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null); ;
        }
        /// <summary>
        /// 执行不返回数据行的操作，返回一个int类型的数据
        /// </summary>
        /// <param name="sql">要执行的SQL文本命令</param>
        /// <param name="sp">T-SQL语句或存储过程的参数数组</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] sp)
        {
            return ExecuteNonQuery(sql, CommandType.Text, sp);
        }
        /// <summary>
        /// 执行不返回数据行的操作，返回一个int类型的数据
        /// </summary>
        /// <param name="sql">要执行的SQL文本命令或存储过程名称</param>
        /// <param name="type">要执行的查询语句的类型，存储过程或SQL文本命令</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, CommandType type)
        {
            return ExecuteNonQuery(sql, type, null);
        }
        /// <summary>
        /// 执行不返回数据行的操作，返回一个int类型的数据
        /// </summary>
        /// <param name="sql">要查询的SQL文本命令或存储过程名称</param>
        /// <param name="type">要执行的查询语句的类型，存储过程或SQL文本命令</param>
        /// <param name="sp">T-SQL语句或存储过程的参数数组</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, CommandType type, SqlParameter[] sp)
        {
            try
            {
                SQLOpen();
                SqlCommand sqlcommand = new SqlCommand(sql, sqlconnection);
                sqlcommand.CommandType = type;
                if (sp != null)
                {
                    foreach (SqlParameter parameter in sp)
                    {
                        sqlcommand.Parameters.Add(parameter);
                    }
                }
                int result = sqlcommand.ExecuteNonQuery();
                return result;

            }
            catch (Exception e)
            {
                throw new Exception("ExecuteNonQuer错误:" + e.Message);
            }
            finally
            {
                SQLClose();
            }

        }


        #endregion

        #region    ExecuteScalar 执行查询，并返回查询结果集中第一行的第一列
        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行的第一列
        /// </summary>
        /// <param name="sql">要执行的SQL文本命令</param>
        /// <returns>返回第一行的第一列</returns>
        public static object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, CommandType.Text, null);
        }
        /// <summary>
        /// 执行查询，并返回查询结果集中地第一行的第一列
        /// </summary>
        /// <param name="sql">要查询的SQL文本命令或存储过程名称</param>
        /// <param name="sp">T-SQL语句或存储过程的参数数组</param>
        /// <returns>返回第一行的第一列</returns>
        public static object ExecuteScalar(string sql, SqlParameter[] sp)
        {
            return ExecuteScalar(sql, CommandType.Text, sp);
        }
        /// <summary>
        /// 执行查询，并返回查询结果集中地第一行的第一列
        /// </summary>
        /// <param name="sql">要查询的SQL文本命令或存储过程名称</param>
        /// <param name="type">要执行的查询语句的类型，存储过程或SQL文本命令</param>
        /// <returns>返回第一行的第一列</returns>
        public static object ExecuteScalar(string sql, CommandType type)
        {
            return ExecuteScalar(sql, type, null);
        }
        /// <summary>
        /// 执行查询，并返回查询结果集中第一行的第一列
        /// </summary>
        /// <param name="sql">要查询的SQL文本命令或存储过程名称</param>
        /// <param name="type">要执行的查询语句的类型，存储过程或SQL文本命令</param>
        /// <param name="sp">T-SQL语句或存储过程的参数数组</param>
        /// <returns>返回第一行的第一列</returns>
        public static object ExecuteScalar(string sql, CommandType type, SqlParameter[] sp)
        {
            try
            {
                object result = null;
                SQLOpen();
                SqlCommand sqlcommmand = new SqlCommand(sql, sqlconnection);
                sqlcommmand.CommandType = type;
                if (sp != null)
                {
                    foreach (SqlParameter parameter in sp)
                    {
                        sqlcommmand.Parameters.Add(parameter);
                    }

                }
                result = sqlcommmand.ExecuteScalar();
                return result;
            }
            catch (Exception e)
            {
                throw new Exception("ExecuteScalar错误：" + e.Message);
            }
            finally
            {
                SQLClose();
            }

        }
        #endregion

        #region   ExecuteReader 执行查询，并返回一个 DataReader 对象
        /// <summary>
        /// 执行查询，并返回一个 DataReader 对象
        /// </summary>
        /// <param name="sql">要执行的SQL文本语句</param>
        /// <returns>返回DataReader对象实例</returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，并返回一个 DataReader 对象
        /// </summary>
        /// <param name="sql">要执行的SQL文本语句</param>
        ///  <param name="sp">T-SQL语句或存储过程的参数数组</param>
        /// <returns>返回DataReader对象实例</returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] sp)
        {
            return ExecuteReader(sql, CommandType.Text, sp);
        }

        /// <summary>
        /// 执行查询，并返回一个 DataReader 对象
        /// </summary>
        /// <param name="sql">要查询的SQL文本命令或存储过程名称</param>
        /// <param name="type">要执行的查询语句的类型，存储过程或SQL文本命令</param>
        /// <returns>返回DataReader对象实例</returns>
        public static SqlDataReader ExecuteReader(string sql, CommandType type)
        {
            return ExecuteReader(sql, type, null);
        }
        /// <summary>
        /// 执行查询，并返回一个 DataReader 对象
        /// </summary>
        /// <param name="sql">要查询的SQL文本命令或存储过程名称</param>
        /// <param name="type">要执行的查询语句的类型，存储过程或SQL文本命令</param>
        /// <param name="sp">T-SQL语句或存储过程的参数数组</param>
        /// <returns>返回DataReader对象实例</returns>
        public static SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] sp)
        {
            try
            {
                SQLOpen();
                SqlCommand sqlcommand = new SqlCommand(sql, sqlconnection);
                sqlcommand.CommandType = type;
                sqlcommand.Parameters.Clear();
                if (sp != null)
                {
                    foreach (SqlParameter parameter in sp)
                    {
                        sqlcommand.Parameters.Add(parameter);
                    }
                }
                SqlDataReader reader = sqlcommand.ExecuteReader();
                sqlcommand.Parameters.Clear();
                return reader;

            }
            catch (Exception e)
            {
                throw new Exception("ExecuteReader错误：" + e.Message);
            }
            finally
            {
                SQLClose();
            }

        }
        /// <summary>
        /// 执行查询，并返回一个 DataReader 对象
        /// </summary>
        /// <param name="sql">要查询的SQL文本命令或存储过程名称</param>
        /// <param name="type">要执行的查询语句的类型，存储过程或SQL文本命令</param>
        /// <param name="sp">T-SQL语句或存储过程的参数数组</param>
        /// <returns>返回DataReader对象实例</returns>
        public static SqlDataReader ExecuteReader(string sql, CommandType type, string cmdText, SqlParameter[] sp)
        {
            try
            {
                SQLOpen();
                SqlCommand sqlcommand = new SqlCommand(sql, sqlconnection);
                sqlcommand.CommandText = cmdText;
                sqlcommand.CommandType = type;
                sqlcommand.Transaction = null;
                sqlcommand.Parameters.Clear();
                if (sp != null)
                {
                    foreach (SqlParameter parameter in sp)
                    {
                        sqlcommand.Parameters.Add(parameter);
                    }
                }
                SqlDataReader reader = sqlcommand.ExecuteReader();
                sqlcommand.Parameters.Clear();
                return reader;
            }
            catch (Exception e)
            {
                throw new Exception("ExecuteReader错误：" + e.Message);
            }
            finally
            {
                SQLClose();
            }

        }
        #endregion


        #region 执行存储过程
        /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SQLOpen();
            SqlCommand sqlcommand = new SqlCommand(storedProcName, sqlconnection);
            sqlcommand.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    sqlcommand.Parameters.Add(parameter);
                }
            }
            SqlDataReader returnReader = sqlcommand.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;

        }
        #endregion

        #region ExecuteDataTable 执行查询，并返回结果集

        /// <summary>
        /// 执行查询，并返回结果集
        /// </summary>
        /// <param name="sql">要执行的SQL文本语句</param>
        /// <returns>查询结果集</returns>
        public static DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，并返回结果集
        /// </summary>
        /// <param name="sql">要执行的SQL文本语句</param>
        /// <param name="sp">T-SQL语句或存储过程的参数组</param>
        /// <returns>查询结果集</returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] sp)
        {
            return ExecuteDataTable(sql, CommandType.Text, sp);
        }

        /// <summary>
        /// 执行查询，并返回结果集
        /// </summary>
        /// <param name="sql">要执行的SQL文本语句或存储过程</param>
        /// <param name="type">查询语句类型，存储过程或SQL文本命令</param>
        /// <returns>查询结果集</returns>
        public static DataTable ExecuteDataTable(string sql, CommandType type)
        {
            return ExecuteDataTable(sql, type, null);
        }

        /// <summary>
        /// 执行查询，并返回结果集
        /// </summary>
        /// <param name="sql">要执行的SQL文本语句或存储过程</param>
        /// <param name="type">查询语句类型，存储过程或SQL文本命令</param>
        /// <param name="sp">T-SQL语句或存储过程的参数组</param>
        /// <returns>查询结果集</returns>
        public static DataTable ExecuteDataTable(string sql, CommandType type, SqlParameter[] sp)
        {
            try
            {
                SQLOpen();
                DataTable dt = new DataTable();
                SqlCommand sqlcommand = new SqlCommand(sql, sqlconnection);
                sqlcommand.CommandType = type;
                if (sp != null)
                {
                    foreach (SqlParameter parameter in sp)
                    {
                        sqlcommand.Parameters.Add(parameter);
                    }
                }
                SqlDataAdapter adapter = new SqlDataAdapter(sqlcommand);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                throw new Exception("ExecuteDataTable错误：" + e.Message);
            }
            finally
            {
                SQLClose();
            }
        }

        #endregion

        #region 返回ArrayList
        /// <summary>
        /// 返回ArrayList
        /// </summary>
        /// <param name="sql">要执行的SQL文本语句或存储过程</param>
        /// <returns>查询结果集</returns>
        public static ArrayList ReturnArrayList(string sql)
        {
            try
            {
                SQLOpen();
                DataTable dt = new DataTable();
                SqlCommand command = new SqlCommand(sql, sqlconnection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);
                return TurnArrayList(dt);
            }
            catch (Exception ex)
            {
                throw new Exception("ReturnArrayList错误：" + ex);
            }
            finally
            {
                SQLClose();
            }
        }
        /// <summary>
        /// 转换为ArrayList
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <returns>ArrayList</returns>
        private static ArrayList TurnArrayList(DataTable dt)
        {
            ArrayList array = new ArrayList();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];

                Hashtable record = new Hashtable();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    object cellValue = row[j];
                    if (cellValue.GetType() == typeof(DBNull))
                    {
                        cellValue = null;
                    }
                    record[dt.Columns[j].ColumnName] = cellValue;
                }
                array.Add(record);
            }
            return array;
        }

        #endregion

    }
}
